/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package Admin.DAO;


import Admin.Model.User;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.taglibs.standard.lang.jpath.adapter.Convert;

/**
 *
 * @author Hoang Quy Hong An
 */
public class userDAO {

    private Connection myCon;
    private PreparedStatement ps;
    private ResultSet rs;
    private String strCommand = "";

//    public boolean addUser(User user) {
// Admin k có quyen them du lieu trong bang nay
//        boolean flag = false;
//
//        try {
//            strCommand = "INSERT INTO tbl_Users (userName ,userEmail ,userPassword ,userAddress ,userPhone ,userTypeID ,userStatus)"
//                    + " VALUES (? ,? ,? ,? ,? ,? ,1)";
//
//            myCon = myConnection.getConnection();
//            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//            ps.setString(1, user.getUserName());
//            ps.setString(2, user.getUserEmail());
//            ps.setString(3, user.getUserPassword());
//            ps.setString(4, user.getUserAddress());
//            ps.setString(5, user.getUserPhone());
//            ps.setInt(6, user.getUserTypeID());
//            int i = ps.executeUpdate();
//            if (i > 0) {
//                flag = true;
//            }
//        } catch (SQLException ex) {
//            ex.printStackTrace();
//        } finally {
//            myConnection.closeConnection(myCon, ps, null);
//        }
//        return flag;
//    }

    public User login(String userEmail, String userPassword) {
        User objUser = null;
        try {
            strCommand = "SELECT * FROM tbl_Users "
                    + "WHERE userEmail = ? AND userPassword = ? AND userStatus = 1 AND userTypeID = 1";

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, userEmail);
            ps.setString(2, userPassword);
            rs = ps.executeQuery();
            while (rs.next()) {
                objUser = new User();
                objUser.setUserID(rs.getInt("userID"));
                objUser.setUserName(rs.getString("userName"));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return objUser;
    }

    public boolean changePassword(User user) {
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_Users SET userPassword = ? WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setString(1, user.getUserPassword());
            ps.setInt(2, user.getUserID());
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public boolean changeUserType(User user) {
        boolean flag = false;
        try {
            strCommand = "UPDATE tbl_Users SET userTypeID = ? WHERE userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, user.getUserTypeID());
            ps.setInt(2, user.getUserID());
            int i = ps.executeUpdate();
            if (i > 0) {
                flag = true;
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return flag;
    }

    public User viewOwnProperty(int userID) {
        User user = null;
        try {
            strCommand = "SELECT tbl_Users.userName, tbl_Users.userEmail, tbl_Users.userAddress, tbl_Users.userPhone, tbl_UserType.userTypeName "
                    + "FROM tbl_Users INNER JOIN "
                    + "tbl_UserType ON tbl_Users.userTypeID = tbl_UserType.userTypeID WHERE  tbl_Users.userID = ?";
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
            ps.setInt(1, userID);
            rs = ps.executeQuery();
            while (rs.next()) {
                user = new User(userID, rs.getString("userName"),
                        rs.getString("userEmail"), rs.getString("userAddress"),
                        rs.getString("userPhone"), rs.getString("userTypeName"));
            }
            return user;
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            myConnection.closeConnection(myCon, ps, null);
        }
        return user;
    }

     public  boolean checkLoginAdmin(String userEmail, String password) {

        boolean flag = false;
        User user = new User();

        try {
            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement("SELECT * FROM tbl_Users "
                    + "WHERE userEmail = ? AND userPassword = ? AND userStatus = 1 AND userTypeID = 1");
            ps.setString(1, userEmail);
            ps.setString(2, password);
            rs = ps.executeQuery();
            while (rs.next()) {
                flag = true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
            myConnection.closeConnection(myCon, ps, rs);
        }
        return flag;
    }

     public ArrayList<User> GetByType (int userTypeID)
    {
         ArrayList<User> listUser= new ArrayList<User>();
         User user=null;
        try {

            strCommand = "select * from tbl_Users WHERE userTypeID=?";
            myCon=myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);
            ps.setInt(1, userTypeID);
            rs=ps.executeQuery();
            while(rs.next())
            {
                user=new User();
                user.setUserID(rs.getInt("userID"));
                user.setUserName(rs.getString("userName"));
                user.setUserEmail(rs.getString("userEmail"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setUserAddress(rs.getString("userAddress"));
                user.setUserPhone(rs.getString("userPhone"));
                user.setUserTypeID(rs.getInt("userTypeID"));
               // user.setUserStatus(rs.getInt("userStatus"));

                listUser.add(user);
            }
            return listUser;
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
              myConnection.closeConnection(myCon, ps, rs);
        }
        return null;
    }

      public User GetByID (int userID)
    {

         User user=new User();
        try {

            strCommand = "select userID, userName, userEmail, userPassword, userAddress, userPhone, userTypeID, userStatus from tbl_Users WHERE userID=?";

            myCon = myConnection.getConnection();
            ps = myCon.prepareStatement(strCommand);
            ps.setInt(1, userID);
            rs=ps.executeQuery();
            while(rs.next())
            {
                user.setUserID(rs.getInt("userID"));
                user.setUserName(rs.getString("userName"));
                user.setUserEmail(rs.getString("userEmail"));
                user.setUserPassword(rs.getString("userPassword"));
                user.setUserAddress(rs.getString("userAddress"));
                user.setUserPhone(rs.getString("userPhone"));
                user.setUserTypeID(rs.getInt("userTypeID"));
              //user.setUserStatus(rs.getInt("userStatus"));


            }
            return user;
        } catch (SQLException ex) {
            Logger.getLogger(User.class.getName()).log(Level.SEVERE, null, ex);
        }finally{
             myConnection.closeConnection(myCon, ps, rs);
        }
        return user;
    }


}